/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package threading_webserver;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import threading_webserver.model.User;

/**
 * @author Koen
 */
public class DatabaseConnector 
{
    private static String host = "jdbc:mysql://localhost:3307/test";
    private static String username = ""; 
    private static String password = "";
    
    private static Connection con;
    
    public static String OpenConnection()
    {
        try 
        {
            con = DriverManager.getConnection(host, username, password);
            return "Connection opened.";
        } catch (SQLException ex) 
        {
            return ex.toString();
        }
    }
    
    /**
     * Checks whether given username and password combination is correct
     * @param username
     * @param password
     * @return 
     * <br>-1 if sqlException
     * <br>0 if incorrect
     * <br>1 if correct, user level 1
     * <br>2 if correct, user level 2
     */
    public static int CheckLoginData(String username, String password)
    {
        try {
            if(con != null && con.isValid(0))
            {
                
                String pass = cryptWithMD5(password);
                PreparedStatement stmt = con.prepareStatement(String.format("SELECT `Level` FROM `users` WHERE `Username`=? AND `Password`=?")); //con.createStatement();
                stmt.setString(1, username);
                stmt.setString(2, pass);
                
                ResultSet rs = stmt.executeQuery();
                
                //SQL injection imminent
                //ResultSet rs = stmt.executeQuery(String.format("SELECT `Level` FROM `users` WHERE `Username`='%s' AND `Password`='%s'", username, pass));
                
                int level = 0;
                
                if(rs.next())
                {
                    level = Integer.parseInt(rs.getString("Level"));
                }
                return level;
            }
            else
            {
                OpenConnection();
                return CheckLoginData(username, password);
            }
        } catch (SQLException ex) {
            return -1;
        }
    }
    
    public static User GetUser(String username)
    {
        try 
        {
            if(con != null && con.isValid(0))
            {
                PreparedStatement stmt = con.prepareStatement(String.format("SELECT `UserDBID`, `Username`, `Password`, `Level`, `Logged` FROM `users` WHERE `Username` = ?"));
                stmt.setString(1, username);
                ResultSet rs = stmt.executeQuery();
                        
                if(rs.next())
                {
                    User user = new User(rs.getString("Username"), rs.getInt("Level"), rs.getInt("logged"));
                    return user;
                }
                else{
                    return null;
                }
            }
            else
            {
                OpenConnection();
                GetUser(username);
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());   
        }
        return null;
    }
        
    public static void AddUser(String username, String password, int level)
    {
        try 
        {
            if(con != null && con.isValid(0))
            {
                String pass = cryptWithMD5(password);
                PreparedStatement stmt = con.prepareStatement(String.format("INSERT INTO `users` (`Username`,`Password`,`Level`) VALUES(?,?,?)"));
                stmt.setString(1, username);
                stmt.setString(2, pass);
                stmt.setInt(3, level);
                stmt.executeUpdate();
                //stmt.executeUpdate(String.format("INSERT INTO `users` (`Username`,`Password`,`Level`) VALUES('%s','%s','%d');", username, pass, level));
            }
            else
            {
                OpenConnection();
                AddUser(username, password, level);
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());   
        }
    }
    
    public static boolean LogUser(String username, int logged)
    {
         try 
        {
            if(con != null && con.isValid(0))
            {
                PreparedStatement stmt = con.prepareStatement(String.format("UPDATE `users` SET `Logged`=? WHERE `Username`=?"));
                stmt.setInt(1, logged);
                stmt.setString(2, username);
                stmt.executeUpdate();
            }
            else
            {
                OpenConnection();
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());   
        }
        return true;
    }
    
    private static String cryptWithMD5(String pass)
    {
        try 
        {
            MessageDigest md = MessageDigest.getInstance("MD5");
            byte[] passBytes = pass.getBytes();
            md.reset();
            byte[] digested = md.digest(passBytes);
            StringBuffer sb = new StringBuffer();
            for(int i=0;i<digested.length;i++){
                sb.append(Integer.toHexString(0xff & digested[i]));
            }
            return sb.toString();
        } catch (NoSuchAlgorithmException ex) {       
            return null;
        }
    }
}
